# @Author ZhangGJ
# @Date 2021/03/24 11:22
import time

import cx_Oracle
import pymysql
from pymysql.cursors import DictCursor
import json

ccc_code = ['5210000125',
            '5200000055',
            '5200000056',
            '5200000058',
            '5190000112',
            '5190000065',
            '5190000070',
            '5190000104',
            '5190000071',
            '5190000074',
            '5190000072',
            '5190000013',
            '5210000127',
            '5190000153',
            '5180000010',
            '5180000062',
            '5180000081',
            '5190000137',
            '5190000138',
            '5190000146',
            '5190000155',
            '5190000229',
            '5190000239',
            '5190000003',
            '5200000021',
            '5210000003',
            '5210000009',
            '5210000013',
            '5210000047',
            '5210000070',
            '5210000017',
            '5210000079',
            '5210000079',
            '5210000092',
            '5210000109',
            '5210000026',
            '5210000094',
            '5210000028',
            '5210000030',
            '5210000140',
            '5210000143',
            '5210000067',
            '5210000083',
            '5210000091',
            '5210000102',
            '5210000242',
            '5210000244',
            '5220000035',
            '5220000039',
            '5220000036',
            '5210000136',
            '8110000015',
            '5190000039',
            '5190000010',
            '5190000014',
            '5190000017',
            '5190000143',
            '5190000159',
            '5190000211',
            '5180000021',
            '5180000085',
            '5180000075',
            '5220000047',
            '5220000046',
            '5220000037',
            '5220000019',
            '5506090300',
            '6651000065',
            '6651000070',
            '6651000037',
            '6651000044',
            '6651000049',
            '6651000053',
            '6651000060',
            '6650000022',
            '6650000023',
            '6650000023',
            '6650000021',
            '6210000120',
            '6190000119',
            '6200000044',
            '6190000048',
            '6190000065',
            '6190000070',
            '6220000060',
            '6220000060',
            '6190000141',
            '6190000182',
            '6650000003',
            '6190000048',
            '6210000003',
            '6210000079',
            '6210000092',
            '6220000095',
            '6610000013',
            '6230000100',
            '6660000005',
            '6620000010',
            '6620000002',
            '6220000018',
            '6231000112',
            '5231000108',
            '6670000001',
            '6650000003',
            '6650000025',
            '6651000039',
            '6651000038',
            '6651000040',
            '6651000041',
            '6650000015',
            '6651000042',
            '6651000043',
            '6650000016',
            '6651000046',
            '6651000048',
            '6650000011',
            '6651000051',
            '6651000050',
            '6651000052',
            '6650000027',
            '6650000029',
            '6651000055',
            '6651000056',
            '6650000007',
            '6651000058',
            '6650000008',
            '6651000062',
            '6651000061',
            '6650000006',
            '6651000063',
            '6620000002',
            '6620000020',
            '6660000012',
            '6660000008',
            '6660000016',
            '6660000010',
            '6660000032',
            '6650000030',
            '6651000069',
            '8220000012',
            '6630000007',
            '6650000031',
            '7120000013',
            '7120000011',
            '7120000014',
            '7170000001',
            '7210000022',
            '7220000108',
            '7220000105',
            '7308060000',
            '8110000010',
            '8190000268',
            '8210000077']

mysql_conn = pymysql.connect(host='localhost', port=3666, user='root',
                             passwd='123456', db='test', charset='utf8')
mysql_cur = mysql_conn.cursor(DictCursor)

mysql_sql = 'SELECT group_concat(id) id from cost_center_code where `code` in %s'
mysql_cur.execute(mysql_sql, (ccc_code,))
rows = mysql_cur.fetchall()
print(f'==============原始数据数量：{len(rows)}')
print(f'====================数据：{rows}')
ccc_dict = list(rows)[0]
ccc_id_str = dict(ccc_dict).get('id')
ccc_id_list = str(ccc_id_str).split(',')

ledger_sql = "select * from assetfinances where assetfinances_attributes ->> '$.groupCCCFinance.value' in %s"
mysql_cur.execute(ledger_sql, (ccc_id_list,))
ledger_rows = mysql_cur.fetchall()

oracle_conn = cx_Oracle.connect("root", "123456", "localhost")
oracle_curs = oracle_conn.cursor()
for ledger_row in ledger_rows:
    ledger_row_dict = dict(ledger_row)
    assetfinances_attributes_json = json.loads(str(ledger_row_dict.get('assetfinances_attributes')))
    oldNumberFinance = assetfinances_attributes_json.get('oldNumberFinance')
    oldNumberFinance_dict = dict(oldNumberFinance)
    oldNumber = oldNumberFinance_dict.get('value')
    if oldNumber is None or oldNumber == '':
        asset_code = ledger_row_dict.get('financial_no')
    else:
        asset_code = oldNumber

    companyFinance = assetfinances_attributes_json.get('companyFinance')
    companyFinance_dict = dict(companyFinance)
    company_text = companyFinance_dict.get('text')
    company_value = companyFinance_dict.get('value')
    entity_name = company_text

    entity_code = company_value

    # 6651000046(SSOC_CNC_SH)
    groupCCCFinance = assetfinances_attributes_json.get('groupCCCFinance')
    groupCCCFinance_dict = dict(groupCCCFinance)
    groupCCCFinance = groupCCCFinance_dict.get('text')
    group_ccc = str(groupCCCFinance)
    ind = group_ccc.index('(')
    new_ccc = group_ccc[:ind]
    ccc = new_ccc

    creation_date = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())

    attribute1 = 2
    attribute2 = ledger_row_dict.get('financial_no')
    attribute3 = company_value
    attribute4 = 'HIFM'
    attribute5 = 'CCC_UPDATE'
    process_flag = 'N'
    oracle_sql = "INSERT INTO CUX.CUX_FA_AMS_ASSET_CHANGE" \
                 "( ENTITY_NAME, ENTITY_CODE, ASSET_CODE, CCC, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, CREATION_DATE, PROCESS_FLAG ) " \
                 "VALUES " \
                 "(:entity_name, :entity_code, :asset_code, :ccc, :attribute1, :attribute2, :attribute3, :attribute4, :attribute5, TO_DATE( :creation_date, 'SYYYY-MM-DD HH24:MI:SS' ), :process_flag)"
    oracle_curs.execute(oracle_sql, {'entity_name': entity_name,
                                     'entity_code': entity_code,
                                     'asset_code': asset_code,
                                     'ccc': ccc,
                                     'attribute1': attribute1,
                                     'attribute2': attribute2,
                                     'attribute3': attribute3,
                                     'attribute4': attribute4,
                                     'attribute5': attribute5,
                                     'creation_date': creation_date,
                                     'process_flag': process_flag
                                     })

mysql_cur.close()
mysql_conn.commit()
mysql_conn.close()

oracle_curs.close()
oracle_conn.commit()
oracle_conn.close()
print('=================Success!')
